using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections;
using System.Configuration;
namespace ProcessesSMS
{
	/// <summary>
	/// Summary description for XOSO_WAIT.
	/// </summary>
	public class XOSO_WAIT
	{
		//connectionstring vit_sel
		private string connstrVIT_SEL =ConfigurationSettings.AppSettings["CONNECTION_STRING_7x62"].ToString();
		//connectionstring gpc
		private string connstrGPC =ConfigurationSettings.AppSettings["SMSConnStr"].ToString();
		//connectionstring sms_active
		private string connString_wait ="";
		
		#region *********Scan table active
		public string GetActiveCHO()
		{
			string strvalue="";
			string strSQl = "select [value],province from active where id=1";
			SqlConnection conn = new SqlConnection(connString_wait);				
			SqlDataAdapter oleda = new SqlDataAdapter(strSQl,conn);				
			DataSet ds = new DataSet();
			oleda.Fill(ds, "VIT");
			conn.Close();
			if(ds.Tables["VIT"].Rows.Count>0)
                strvalue = ds.Tables["VIT"].Rows[0]["value"].ToString().Trim() + "," + ds.Tables["VIT"].Rows[0]["province"].ToString().Trim();
            ds.Dispose();
            conn.Dispose();
            return strvalue;
		}
		public string GetActiveTT()
		 {
			 string strvalue="";
             string strSQl = "select [value],province from active where id=2";
			 SqlConnection conn = new SqlConnection(connString_wait);				
			 SqlDataAdapter oleda = new SqlDataAdapter(strSQl,conn);				
			 DataSet ds = new DataSet();
			 oleda.Fill(ds, "VIT");
			 conn.Close();
             if (ds.Tables["VIT"].Rows.Count > 0)
                 strvalue = ds.Tables["VIT"].Rows[0]["value"].ToString().Trim() + "," + ds.Tables["VIT"].Rows[0]["province"].ToString().Trim();
             ds.Dispose();
             conn.Dispose();
             return strvalue;
		 }
		public void ResetActiveCHO()
		{
			string strSQl = "update active set value=0,province='' where id=1";
			SqlConnection conn = new SqlConnection(connString_wait);
			conn.Open();
			SqlCommand cmd = new SqlCommand(strSQl,conn);
			cmd.ExecuteNonQuery();
            conn.Close();
            conn.Dispose();
		}
		public void ResetActiveTT()
		{
            string strSQl = "update active set value=0,province='' where id=2";
			SqlConnection conn = new SqlConnection(connString_wait);
			conn.Open();
			SqlCommand cmd = new SqlCommand(strSQl,conn);
			cmd.ExecuteNonQuery();
            conn.Close();
            conn.Dispose();
		}
		#endregion
		
		#region ********InsertMT
		private int InsertMT(int mo_id,string User_id,string ServiceID,string CommandCode,string Message,int RequestID,int MsgType,int ContentType)
		{	
			int val=0;
			string reqSQL = "INSERT INTO SMS_MT(MO_ID,UserID,ReceiverUserID,ServiceID,CommandCode,Message,RequestID,MsgType,ContentType,RequestTime)";
			reqSQL+= " VALUES(@MO_ID,@UserID,@ReceiverUserID,@ServiceID,@CommandCode,@Message,@RequestID,@MsgType,@ContentType,@RequestTime)";
			try
			{
				SqlConnection conn = new SqlConnection(connstrGPC);
				conn.Open();
				SqlCommand oCommand=new SqlCommand(reqSQL,conn);
				oCommand.Parameters.Clear();
				oCommand.Parameters.AddWithValue("@MO_ID",mo_id);
				oCommand.Parameters.AddWithValue("@UserID",User_id);
				oCommand.Parameters.AddWithValue("@ReceiverUserID","");
				oCommand.Parameters.AddWithValue("@ServiceID",ServiceID);
				oCommand.Parameters.AddWithValue("@CommandCode",CommandCode);
				oCommand.Parameters.AddWithValue("@Message",Message);
				oCommand.Parameters.AddWithValue("@RequestID",RequestID);
				oCommand.Parameters.AddWithValue("@MsgType",MsgType);
				oCommand.Parameters.AddWithValue("@ContentType",ContentType);
				oCommand.Parameters.AddWithValue("@RequestTime",DateTime.Now);
				oCommand.ExecuteNonQuery();				
				conn.Close();
                conn.Dispose();
				val=1;
			}			
			catch(Exception myErr)
			{
				throw(new Exception(myErr.ToString() + reqSQL));				
			}
			return val;
		}
		#endregion

		#region *********SMS_WAIT 
		private void UpdateSentCounterSMS_WAIT(int mo_id)
		{
			string strSQl = "update sms_wait set sentcounter=(sentcounter -1) where mo_id=" + mo_id;
			SqlConnection conn = new SqlConnection(connstrGPC);
			conn.Open();
			SqlCommand cmd = new SqlCommand(strSQl,conn);
			cmd.ExecuteNonQuery();
            conn.Close();
            conn.Dispose();
		}
		private void ResetCounterTT()
		{
			string strSQl = "update SMS_WAIT set SentCounter=0 where TT=1";
			SqlConnection conn = new SqlConnection(connstrGPC);
			conn.Open();
			SqlCommand cmd = new SqlCommand(strSQl,conn);
			cmd.ExecuteNonQuery();
            conn.Close();
            conn.Dispose();
		}
		public int InsetSMS_WAIT(int mo_id,string User_id,string ServiceID,string CommandCode,string SystaxCode,int RequestID,DateTime RequestTime,int tt,int SentCouter)
		{
			int val=0;
			string reqSQL = "INSERT INTO SMS_WAIT(MO_ID,UserID,ServiceID,CommandCode,SystaxCode,RequestID,RequestTime,tt,SentCounter)";
			reqSQL+= " VALUES(@MO_ID,@UserID,@ServiceID,@CommandCode,@SystaxCode,@RequestID,@RequestTime,@tt,@SentCounter)";
			try
			{
				SqlConnection conn = new SqlConnection(connstrGPC);
				conn.Open();
				SqlCommand oCommand=new SqlCommand(reqSQL,conn);
				oCommand.Parameters.Clear();
				oCommand.Parameters.AddWithValue("@MO_ID",mo_id);
				oCommand.Parameters.AddWithValue("@UserID",User_id);				
				oCommand.Parameters.AddWithValue("@ServiceID",ServiceID);
				oCommand.Parameters.AddWithValue("@CommandCode",CommandCode);
				oCommand.Parameters.AddWithValue("@SystaxCode",SystaxCode);
				oCommand.Parameters.AddWithValue("@RequestID",RequestID);
				oCommand.Parameters.AddWithValue("@RequestTime",RequestTime);			
				oCommand.Parameters.AddWithValue("@tt",tt);
				oCommand.Parameters.AddWithValue("@SentCounter",SentCouter);			
				oCommand.ExecuteNonQuery();				
				conn.Close();
                conn.Dispose();
				val=1;
			}			
			catch(Exception myErr)
			{
				throw(new Exception(myErr.ToString() + reqSQL));				
			}
			return val;
		}
		public void SendSMS_WAITMsgCHO(string MT)
		{
            //Lay ra tat ca nhung thue bao dk CHO nhan kqxs cua tinh MT
            string strSQl = "select * from SMS_WAIT where sentCounter>0 and TT=0 and SystaxCode='" + MT + "'";
			SqlConnection conn = new SqlConnection(connstrGPC);				
			SqlDataAdapter oleda = new SqlDataAdapter(strSQl,conn);			
			DataSet ds = new DataSet();
			oleda.Fill(ds, "VIT");
			conn.Close();
			int i,TotalRecord=ds.Tables["VIT"].Rows.Count;		
			if(TotalRecord>0)
			{
                for (i = 0; i < TotalRecord; i++)
                {
                    string Message = string.Empty;
                    int mo_id = int.Parse(ds.Tables["VIT"].Rows[i]["MO_ID"].ToString());
                    string User_id = ds.Tables["VIT"].Rows[i]["UserID"].ToString();
                    string CommandCode = ds.Tables["VIT"].Rows[i]["CommandCode"].ToString();
                    string ServiceID = ds.Tables["VIT"].Rows[i]["ServiceID"].ToString();
                    string SystaxCode = ds.Tables["VIT"].Rows[i]["SystaxCode"].ToString().Trim();
                    DateTime requestTime = DateTime.Parse(ds.Tables["VIT"].Rows[i]["RequestTime"].ToString());
                    int RequestID = int.Parse(ds.Tables["VIT"].Rows[i]["RequestID"].ToString());
                    int MsgType = 1, ContentType = 0;
                    //Lay kqxs cua tinh MT
                    Message = GetMsg(MT).Split(',')[1];
                    if (!string.IsNullOrEmpty(Message))
                    {                     
                        InsertMT(mo_id, User_id, ServiceID, CommandCode, Message, RequestID, MsgType, ContentType);                     
                        UpdateSentCounterSMS_WAIT(mo_id);
                    }
                }
			}
			ds.Dispose();
            conn.Dispose();
		}
		
		public void SendSMS_WAITMsgTT()
		{
			string strSQl = "select * from SMS_WAIT where sentCounter>0 and TT=1";
			SqlConnection conn = new SqlConnection(connstrGPC);				
			SqlDataAdapter oleda = new SqlDataAdapter(strSQl,conn);			
			DataSet ds = new DataSet();
			oleda.Fill(ds, "VIT");
			conn.Close();
			int i,TotalRecord=ds.Tables["VIT"].Rows.Count;		
			if(TotalRecord>0)
			{				
				for(i=0;i<TotalRecord;i++)
				{
					string Message=string.Empty;
					int mo_id = int.Parse(ds.Tables["VIT"].Rows[i]["MO_ID"].ToString());
					string User_id = ds.Tables["VIT"].Rows[i]["UserID"].ToString();
					string CommandCode = ds.Tables["VIT"].Rows[i]["CommandCode"].ToString();															
					string ServiceID = ds.Tables["VIT"].Rows[i]["ServiceID"].ToString();
					string SystaxCode = ds.Tables["VIT"].Rows[i]["SystaxCode"].ToString();
					DateTime requestTime = DateTime.Parse(ds.Tables["VIT"].Rows[i]["RequestTime"].ToString());
					int RequestID=int.Parse(ds.Tables["VIT"].Rows[i]["RequestID"].ToString());					
					int MsgType=1,ContentType=0;					
					Message = GetMsg("TD");
					//send msg
					InsertMT(mo_id,User_id,ServiceID,CommandCode,Message.Split(',')[1],RequestID,MsgType,ContentType);
					//update couter
					UpdateSentCounterSMS_WAIT(mo_id);
					//neu da quay xong, reset toan bo ca cu phat TT
					if(int.Parse(Message.Split(',')[0])==1)
						ResetCounterTT();
				}
			}
			ds.Dispose();
            conn.Dispose();
		}
		
		#endregion

		#region ********GetMsgbody
		//Tra ra ket qua + ki tu dieu khien da ket thuc chua?
		private string GetMsg(string Ext)
		{			
			string strSQl = string.Empty;
		    int intVal=0;
			if(Ext =="TD")
			{strSQl= "SELECT TOP 1 * FROM XOSO WHERE Mamien='MB' order by Ngay desc";}
			else
			{strSQl= "SELECT TOP 1 * FROM XOSO WHERE Matinh='" + Ext + "' order by Ngay desc";}
			SqlConnection oledbConn = new SqlConnection(connstrVIT_SEL);
			SqlDataAdapter oleda = new SqlDataAdapter(strSQl, oledbConn);					
			DataSet ds = new DataSet();
			oleda.Fill(ds, "VIT");
			oledbConn.Close();		
			string msgBody=string.Empty;			
			if (ds.Tables[0].Rows.Count>0)
			{
				DateTime dateTime = DateTime.Parse(ds.Tables["VIT"].Rows[0]["Ngay"].ToString());
				msgBody += Ext + ":" + dateTime.Day + "/" + dateTime.Month + "\n";

				if(ds.Tables["VIT"].Rows[0]["GiaiDacBiet"].ToString().Trim() !="")
				{ 
					//Da quay xong
					intVal=1;
					msgBody +=	"DB:" + ds.Tables["VIT"].Rows[0]["GiaiDacBiet"].ToString().Trim().Replace(" ","") + "\n";
				}

				if(ds.Tables["VIT"].Rows[0]["GiaiNhat"].ToString().Trim() !="")
					msgBody +=	"1:"+ds.Tables["VIT"].Rows[0]["GiaiNhat"].ToString().Trim().Replace(" ","")+"\n";

				if(ds.Tables["VIT"].Rows[0]["GiaiNhi"].ToString().Trim() !="")
					msgBody +=	"2:"+ds.Tables["VIT"].Rows[0]["GiaiNhi"].ToString().Trim().Replace(" ","")+"\n";

				if(ds.Tables["VIT"].Rows[0]["GiaiBa"].ToString().Trim() !="")
					msgBody +=	"3:"+ds.Tables["VIT"].Rows[0]["GiaiBa"].ToString().Trim().Replace(" ","")+"\n";

				if(ds.Tables["VIT"].Rows[0]["GiaiTu"].ToString().Trim() !="")
					msgBody +=	"4:"+ds.Tables["VIT"].Rows[0]["GiaiTu"].ToString().Trim().Replace(" ","")+"\n";

				if(ds.Tables["VIT"].Rows[0]["GiaiNam"].ToString().Trim() !="")
					msgBody +=	"5:"+ds.Tables["VIT"].Rows[0]["GiaiNam"].ToString().Trim().Replace(" ","")+"\n";

				if(ds.Tables["VIT"].Rows[0]["GiaiSau"].ToString().Trim() !="")
					msgBody +=	"6:"+ds.Tables["VIT"].Rows[0]["GiaiSau"].ToString().Trim().Replace(" ","")+"\n";

				if(ds.Tables["VIT"].Rows[0]["GiaiBay"].ToString().Trim() !="")
					msgBody +=	"7:"+ds.Tables["VIT"].Rows[0]["GiaiBay"].ToString().Trim().Replace(" ","")+"\n";

				if(ds.Tables["VIT"].Rows[0]["GiaiTam"].ToString().Trim() !="")
					msgBody +=  "8:" + ds.Tables["VIT"].Rows[0]["GiaiTam"].ToString().Trim().Replace(" ","");

			}			
			ds.Dispose();
            oledbConn.Dispose();
            //if(!string.IsNullOrEmpty(msgBody) 
			return intVal.ToString() + "," + msgBody;
		}
		#endregion
	}
}
